use strict;
use DBI;
use DBD::ODBC;

#subroutine to trim whitespaces in a string variable
sub trim {
    my($str) = shift =~ m!^\s*(.+?)\s*$!i;
    defined $str ? return $str : return '';
}

# Set connection parameters
my $dsn = 'dbi:ODBC:NTDBDriver';
my ($username, $password) = ('trauma', 'trauma');
print "username = $username, password = $password\n";
# Connect to the database
my $ntdb = DBI -> connect($dsn  ,$username,$password);

# Writing, printing, preparing and executing sql query
my $sql = "SELECT INTUB.INC_KEY, INTUB.INTUB_LOC, INTUB.INTUB_TYPE FROM RDS_INTUB AS INTUB JOIN RDS_DEMO AS DEMO ON DEMO.INC_KEY = INTUB.INC_KEY WHERE DEMO.AGE <= 14 AND DEMO.AGE >= 0";
print "SQL COMMAND EXECUTED: $sql\n";
my $sth = $ntdb -> prepare($sql);
$sth -> execute();

my ($INC_KEY, $INTUB_LOC, $INTUB_TYPE);
my $iter = 0;

my (%hINC_KEY, %hINTUB_LOC, %hINTUB_TYPE);
my (%hINC_KEY_INTUB_LOC, %hINC_KEY_INTUB_TYPE);

while(($INC_KEY, $INTUB_LOC, $INTUB_TYPE) = $sth -> fetchrow_array())
{
    $INC_KEY = trim($INC_KEY); $INTUB_LOC = trim($INTUB_LOC); $INTUB_TYPE = trim($INTUB_TYPE);
    
    $hINC_KEY{$INC_KEY} = 1;
    $hINTUB_LOC{$INTUB_LOC} = 1;
    $hINTUB_TYPE{$INTUB_TYPE} = 1;

    $hINC_KEY_INTUB_LOC{$INTUB_LOC}{$INC_KEY} = 1;
    $hINC_KEY_INTUB_TYPE{$INTUB_LOC}{$INTUB_TYPE}{$INC_KEY} = 1;
    
    $iter++;
    print $iter, "\t", $INC_KEY, "\t", $INTUB_LOC, "\t", $INTUB_TYPE, "\n";
}
print "Total lines = $iter, number of patients = " . (keys %hINC_KEY) . "\n\n";

foreach my $ind (sort keys %hINTUB_LOC)
{
    print "$ind\t". (keys %{$hINC_KEY_INTUB_LOC{$ind}}) . "\n";
}
print "total keys = ". (keys %hINC_KEY_INTUB_TYPE) ."\n";

foreach my $ind1 (sort keys %hINC_KEY_INTUB_TYPE)
{
    foreach my $ind2 (sort keys %{$hINC_KEY_INTUB_TYPE{$ind1}})
    {
        print "$ind1\t$ind2\t". (keys %{$hINC_KEY_INTUB_TYPE{$ind1}{$ind2}}) . "\n";
    }
}
print "\n";